ALTER    PROCEDURE sp_pricing_report_proc
@Customer_Id VARCHAR(15),
@Product_Id VARCHAR(15)

AS

Begin
	declare @CustomerName varchar(65)
	declare @MarketingPartNumber   varchar(100)
	declare @FromQty      int
	declare @ToQty        int 
	declare @UnitPrice    float 


	declare @results table(CustomerName varchar(65),MarketingPartNumber varchar(100),FromQty int,ToQty int,UnitPrice float,PriceType varchar(15)) 
		
	IF (@Customer_Id = '0' AND @Product_Id = '0')
	BEGIN
		declare customerPricingCursor cursor for
		select C_Name , P_Marketing_Part_Number , CPT_Qty_From , CPT_Qty_To , CPT_Unit_Price
		from Customer CUST , Product PRO , Customer_Pricing_Table CPT
		where CUST.Customer_ID = CPT.Customer_ID
		and PRO.Product_ID = CPT.Product_ID
	END
	ELSE IF (@Customer_Id = '0' AND @Product_Id <> '0')
	BEGIN
		declare customerPricingCursor cursor for
		select C_Name , P_Marketing_Part_Number , CPT_Qty_From , CPT_Qty_To , CPT_Unit_Price
		from Customer CUST , Product PRO , Customer_Pricing_Table CPT
		where CUST.Customer_ID = CPT.Customer_ID
		and PRO.Product_ID = @Product_Id AND PRO.Product_ID = CPT.Product_ID
	END
	ELSE IF (@Customer_Id <> '0' AND @Product_Id = '0')
	BEGIN
		declare customerPricingCursor cursor for
		select C_Name , P_Marketing_Part_Number , CPT_Qty_From , CPT_Qty_To , CPT_Unit_Price
		from Customer CUST , Product PRO , Customer_Pricing_Table CPT
		where CUST.Customer_ID = @Customer_Id AND CUST.Customer_ID = CPT.Customer_ID
		and PRO.Product_ID = CPT.Product_ID
	END
	ELSE IF (@Customer_Id <> '0' AND @Product_Id <> '0')
	BEGIN
		declare customerPricingCursor cursor for
		select C_Name , P_Marketing_Part_Number , CPT_Qty_From , CPT_Qty_To , CPT_Unit_Price
		from Customer CUST , Product PRO , Customer_Pricing_Table CPT
		where CUST.Customer_ID = @Customer_Id AND CUST.Customer_ID = CPT.Customer_ID
		and PRO.Product_ID = @Product_Id AND PRO.Product_ID = CPT.Product_ID
	END



	open customerPricingCursor

	fetch next from customerPricingCursor into @CustomerName,@MarketingPartNumber,@FromQty,@ToQty,@UnitPrice
	while @@fetch_status = 0
	begin
		insert into @results(CustomerName,MarketingPartNumber,FromQty,ToQty,UnitPrice,PriceType) 
		values(@CustomerName,@MarketingPartNumber,@FromQty,@ToQty,@UnitPrice,'CUSTOMER')

		fetch next from customerPricingCursor into @CustomerName,@MarketingPartNumber,@FromQty,@ToQty,@UnitPrice
	end
	
	close customerPricingCursor
	deallocate customerPricingCursor

	IF (@Customer_Id = '0')
	BEGIN

		IF (@Product_Id = '0')
		BEGIN
			declare standardPricingCursor cursor for
			select P_Marketing_Part_Number , PPT_Qty_From , PPT_Qty_To , PPT_Unit_Price
			from Product PRO , Product_Price_Table PPT
			where PRO.Product_ID = PPT.Product_ID
		END
		ELSE IF (@Product_Id <> '0')
		BEGIN
			declare standardPricingCursor cursor for
			select P_Marketing_Part_Number , PPT_Qty_From , PPT_Qty_To , PPT_Unit_Price
			from Product PRO , Product_Price_Table PPT
			where PRO.Product_ID = @Product_Id AND PRO.Product_ID = PPT.Product_ID
		END


		open standardPricingCursor
	
		fetch next from standardPricingCursor into @MarketingPartNumber,@FromQty,@ToQty,@UnitPrice
		while @@fetch_status = 0
		begin
			PRINT @PRODUCT_ID
			insert into @results(CustomerName,MarketingPartNumber,FromQty,ToQty,UnitPrice,PriceType) 
			values('',@MarketingPartNumber,@FromQty,@ToQty,@UnitPrice,'STANDARD')
			
			fetch next from standardPricingCursor into @MarketingPartNumber,@FromQty,@ToQty,@UnitPrice
		end
		
		close standardPricingCursor
		deallocate standardPricingCursor
	END	

	/*Modified by lavanya to order by customer name and part number

	select * from @results 
	*/
	select * from @results order by CustomerName,MarketingPartNumber
	/* end of modification by lavanya*/

end





